package com.example.qx.database;

import android.annotation.SuppressLint;

import com.example.qx.lei.itemqxq;
import com.example.qx.lei.leilianxr;
import com.example.qx.lianxra.xiaoxijilu;
import com.mysql.fabric.jdbc.FabricMySQLConnectionProxy;

import java.security.PublicKey;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
import java.util.concurrent.CountDownLatch;

public class data  {

     private Connection[] connection={null};

    public data()  {
        }


    public  String hdmima( int a,Connection connection1) throws SQLException {
        final String[] b = {null};
                    String sql="SELECT * FROM zhangmi WHERE zhanghao = ?";
                    PreparedStatement ps=connection1.prepareStatement(sql);
                    ps.setInt(1,a);
                    ResultSet rs=ps.executeQuery();


                    if(rs!=null){
                        while (rs.next()){
                            b[0] =rs.getString("mima");
                        }
                    }

                    return b[0];
    }

    public int hdzhanghao(int a,Connection connection1) throws SQLException {
        final int[] b = {0};

                    String sql="SELECT * FROM zhangmi WHERE zhanghao = ?";
                    PreparedStatement ps=connection1.prepareStatement(sql);
                    ps.setInt(1,a);
                    ResultSet rs=ps.executeQuery();
                    if(rs!=null)
                    { while(rs.next())
                    {
                        int d=rs.getInt("zhanghao");
                        if(d==a)
                            b[0] =1;
                    }

                    }


             return b[0];
    }
    public leilianxr getgrxx( int a,Connection connection1)   {
          leilianxr leilianxr=new leilianxr(null,null,0,' ',"");
    try{
        String sql="SELECT * FROM zhangmi WHERE zhanghao = ?";
        PreparedStatement ps=connection1.prepareStatement(sql);
        ps.setInt(1,a);
        ResultSet rs=ps.executeQuery();
        if(rs!=null)
        {  while (rs.next())
        {  leilianxr.setId(rs.getInt("zhanghao"));
            leilianxr.setMingzi(rs.getString("mingzi"));
            leilianxr.setTouxiang(rs.getString("tx"));}

        }
    } catch (SQLException e){
        e.printStackTrace();
    }




        return leilianxr;
      }
    public void insertzhangmi(int a,String b,String c,Connection connection1) throws SQLException {

                    String sql="INSERT INTO zhangmi (zhanghao, mima, mingzi, bj,tx) VALUES (?,?,?,?,?)";
                    PreparedStatement ps=connection1.prepareStatement(sql);
                    ps.setInt(1,a);
                    ps.setString(2,b);
                    ps.setString(3,c);
                    ps.setString(4,"s");
                    ps.setString(5,"s");
                    ps.execute();

    }
    public void insertqxq(String a,String b,int c,int d,Connection connection1) throws SQLException {
                    String sql="INSERT INTO qxq (xiangfa, ths, id,sum) VALUES (?,?,?,?)";
                    PreparedStatement ps=connection1.prepareStatement(sql);
                    ps.setString(1,a);
                    ps.setString(2,b);
                    ps.setInt(3,c);
                    ps.setInt(4,d);
                    ps.execute();


    }

    public void deleteqxq(int  a,int b) throws SQLException {
        CountDownLatch latch=new CountDownLatch(1);
        new Thread(new Runnable() {
            @Override
            public void run() {
                try {Class.forName("com.mysql.jdbc.Driver").newInstance();}
                catch (IllegalAccessException | ClassNotFoundException | InstantiationException e) {
                    throw new RuntimeException(e);}
                try {connection[0]= DriverManager.getConnection("jdbc:mysql://sh-cdb-9761pjk2.sql.tencentcdb.com:29081/youqi","root","asd123456");
                    String sql="DELETE FROM qxq WHERE id =? AND sum=?";
                    PreparedStatement ps=connection[0].prepareStatement(sql);
                    ps.setInt(1,a);
                    ps.setInt(2,b);
                    ps.execute();

                } catch (SQLException e) {throw new RuntimeException(e);}
                latch.countDown();
            }
        }).start();
        try{
            latch.await();
            connection[0].close();
        }
        catch (InterruptedException e)
        {e.printStackTrace();}

    }
    public void deleteqb() throws SQLException {
        CountDownLatch latch=new CountDownLatch(1);
        new Thread(new Runnable() {
            @Override
            public void run() {
                try {Class.forName("com.mysql.jdbc.Driver").newInstance();}
                catch (IllegalAccessException | ClassNotFoundException | InstantiationException e) {
                    throw new RuntimeException(e);}
                try {connection[0]= DriverManager.getConnection("jdbc:mysql://sh-cdb-9761pjk2.sql.tencentcdb.com:29081/youqi","root","asd123456");

                    String sql="TRUNCATE TABLE qxq";
                    PreparedStatement ps=connection[0].prepareStatement(sql);
                    ps.execute();
                    String sql1="TRUNCATE TABLE zhangmi";
                    PreparedStatement ps1=connection[0].prepareStatement(sql);
                    ps1.execute();
                    String sql2="TRUNCATE TABLE gx";
                    PreparedStatement ps2=connection[0].prepareStatement(sql);
                    ps2.execute();
                    String sql3="TRUNCATE TABLE chedui";
                    PreparedStatement ps3=connection[0].prepareStatement(sql);
                    ps3.execute();
                } catch (SQLException e) {throw new RuntimeException(e);}
                latch.countDown();
            }
        }).start();
        try{
            latch.await();
            connection[0].close();
        }
        catch (InterruptedException e)
        {e.printStackTrace();}


    }

    @SuppressLint("Range")
    public List<itemqxq> getallqxq(int f,Connection connection1) {

             List<itemqxq> list =new ArrayList<>();
         try{
             String sql1="SELECT * FROM zhangmi WHERE zhanghao = ?";
             PreparedStatement ps1=connection1.prepareStatement(sql1);
             ps1.setInt(1,f);
             ResultSet rs1=ps1.executeQuery();
             String d = null,e=null;
             if(rs1!=null)
             {
                 while (rs1.next())
                 { d=rs1.getString("tx");
                     e=rs1.getString("mingzi");}


                 String sql="SELECT * FROM qxq WHERE id = ?";
                 PreparedStatement ps=connection1.prepareStatement(sql);
                 ps.setInt(1,f);
                 ResultSet rs=ps.executeQuery();
                 if(rs!=null)
                 {
                     while(rs.next()){
                         itemqxq itemqxq =new itemqxq(-1,"","","","",0);
                         String a,b;
                         int c,g;
                         a=rs.getString("xiangfa");
                         b=rs.getString("ths");
                         c=rs.getInt("id");
                         g=rs.getInt("sum");
                         itemqxq.setXiangfa(a);
                         itemqxq.setUri(b);
                         itemqxq.setId(c);
                         itemqxq.setTouxiang(d);
                         itemqxq.setMingzi(e);
                         itemqxq.setSum(g);
                         list.add(itemqxq);
                     }
                 } }
         }catch (SQLException e){
             e.printStackTrace();
         }



          return list;
           }
    public void insertchedui(String a,int b) throws SQLException {
        CountDownLatch latch=new CountDownLatch(1);
        new Thread(new Runnable() {
            @Override
            public void run() {
                try {Class.forName("com.mysql.jdbc.Driver").newInstance();}
                catch (IllegalAccessException | ClassNotFoundException | InstantiationException e) {
                    throw new RuntimeException(e);}
                try {connection[0]= DriverManager.getConnection("jdbc:mysql://sh-cdb-9761pjk2.sql.tencentcdb.com:29081/youqi","root","asd123456");
                    String sql="INSERT INTO chedui (duiming, renshu) VALUES (?,?)";
                    PreparedStatement ps=connection[0].prepareStatement(sql);
                    ps.setString(1,a);
                    ps.setInt(2,b);
                    ps.execute();

                } catch (SQLException e) {throw new RuntimeException(e);}
                latch.countDown();
            }
        }).start();
        try{
            latch.await();
            connection[0].close();
        }
        catch (InterruptedException e)
        {e.printStackTrace();}

    }
    public void updatebjtx(String a,int id,int c) throws SQLException {
        CountDownLatch latch=new CountDownLatch(1);
        new Thread(new Runnable() {
            @Override
            public void run() {
                try {Class.forName("com.mysql.jdbc.Driver").newInstance();}
                catch (IllegalAccessException | ClassNotFoundException | InstantiationException e) {
                    throw new RuntimeException(e);}
                try {connection[0]= DriverManager.getConnection("jdbc:mysql://sh-cdb-9761pjk2.sql.tencentcdb.com:29081/youqi","root","asd123456");
                    if(c==1)
                    {
                        String sql="UPDATE zhangmi SET bj =? WHERE zhanghao =?";
                        PreparedStatement ps=connection[0].prepareStatement(sql);
                        ps.setString(1,a);
                        ps.setInt(2,id);
                        ps.execute();

                    }
                    else if(c==2)
                    {
                        String sql="UPDATE zhangmi SET tx =? WHERE zhanghao =?";
                        PreparedStatement ps=connection[0].prepareStatement(sql);
                        ps.setString(1,a);
                        ps.setInt(2,id);
                        ps.execute();
                    }

                } catch (SQLException e) {throw new RuntimeException(e);}
                latch.countDown();
            }
        }).start();
        try{
            latch.await();
            connection[0].close();
        }
        catch (InterruptedException e)
        {e.printStackTrace();}


    }
    public leilianxr getbjtx( int id,Connection connection1)  {

           leilianxr leilianxr=new leilianxr("","",id,' ',"");

                 try{
                     String sql="SELECT * FROM zhangmi WHERE zhanghao = ?";
                     PreparedStatement ps=connection1.prepareStatement(sql);
                     ps.setInt(1,id);
                     ResultSet rs=ps.executeQuery();

                     if (rs != null) {
                         while (rs.next()) {
                             String a, b;
                             a = rs.getString("bj");
                             b= rs.getString("tx");
                             leilianxr.setBeijing(a);
                             leilianxr.setTouxiang(b);
                         }


                     }
                 }catch (SQLException e){e.printStackTrace();}


           return leilianxr;

       }
@SuppressLint("Range")
public int  sfcunzai( int id, int id1) throws SQLException {
    CountDownLatch latch=new CountDownLatch(1);
    final int[] a = {-1};
    new Thread(new Runnable() {
        @Override
        public void run() {
            try {Class.forName("com.mysql.jdbc.Driver").newInstance();}
            catch (IllegalAccessException | ClassNotFoundException | InstantiationException e) {
                throw new RuntimeException(e);}
            try {connection[0]= DriverManager.getConnection("jdbc:mysql://sh-cdb-9761pjk2.sql.tencentcdb.com:29081/youqi","root","asd123456");
                String sql="SELECT * FROM gx WHERE id=? AND id1=?";
                PreparedStatement ps=connection[0].prepareStatement(sql);
                ps.setInt(1,id);
                ps.setInt(2,id1);
                ResultSet rs=ps.executeQuery();


                if(rs!=null)
                {
                    while (rs.next()){
                        if(rs.getInt("py")==1)
                            a[0] =1;
                        if(rs.getInt("py")==0)
                            a[0] =0;
                    }
                }
                String sql1="SELECT * FROM gx WHERE id=? AND id1 =?";
                PreparedStatement ps1=connection[0].prepareStatement(sql);
                ps1.setInt(1,id1);
                ps1.setInt(2,id);
                ResultSet rs1=ps.executeQuery();
                if(rs1!=null)
                {
                    while (rs1.next()){
                        if(rs1.getInt("py")==1)
                            a[0] =1;
                        if(rs1.getInt("py")==0)
                            a[0] =0;
                    }
                }

            } catch (SQLException e) {throw new RuntimeException(e);}
            latch.countDown();
        }
    }).start();
    try{
        latch.await();
        connection[0].close();
    }
    catch (InterruptedException e)
    {e.printStackTrace();}

        return a[0];
    }
    public void insertpy(int id,int id1) throws SQLException {
        CountDownLatch latch=new CountDownLatch(1);
        new Thread(new Runnable() {
            @Override
            public void run() {
                try {Class.forName("com.mysql.jdbc.Driver").newInstance();}
                catch (IllegalAccessException | ClassNotFoundException | InstantiationException e) {
                    throw new RuntimeException(e);}
                try {connection[0]= DriverManager.getConnection("jdbc:mysql://sh-cdb-9761pjk2.sql.tencentcdb.com:29081/youqi","root","asd123456");
                    String sql="INSERT INTO gx (id, id1, py) VALUES (?,?,?)";
                    PreparedStatement ps=connection[0].prepareStatement(sql);
                    ps.setInt(1,id);
                    ps.setInt(2,id1);
                    ps.setInt(3,0);
                    ps.execute();

                } catch (SQLException e) {throw new RuntimeException(e);}
                latch.countDown();
            }
        }).start();
        try{
            latch.await();
            connection[0].close();
        }
        catch (InterruptedException e)
        {e.printStackTrace();}

    }

public List<Integer>  getpy( int id,int z,Connection connection1) throws SQLException {
        List<Integer> list=new ArrayList<>();
        String sql="SELECT * FROM gx WHERE id = ?";
                PreparedStatement ps=connection1.prepareStatement(sql);
                ps.setInt(1,id);
                ResultSet rs=ps.executeQuery();
                if(z==1){
                    if(rs!=null){
                        while(rs.next()&&(rs.getInt("py")==1))
                        {  int a;
                                a=rs.getInt("id1");

                            list.add(a);
                        }
                    }
                    String sql2="SELECT * FROM gx WHERE id1 = ?";
                    PreparedStatement ps2=connection1.prepareStatement(sql2);
                    ps2.setInt(1,id);
                    ResultSet rs2=ps2.executeQuery();
                    if(rs2!=null){
                        while(rs2.next()&&(rs2.getInt("py")==1))
                        {  int a;
                                a=rs2.getInt("id");
                            list.add(a);
                        }
                    }
                }
                else if(z==0)
                {
                    String sql1="SELECT * FROM gx WHERE id1 = ?";
                    PreparedStatement ps1=connection1.prepareStatement(sql1);
                    ps1.setInt(1,id);
                    ResultSet rs1=ps1.executeQuery();
                    if(rs1!=null){
                        while (rs1.next()&&rs1.getInt("py")==0)
                        { int a;
                            a=rs1.getInt("id");
                            list.add(a);
                        }
                    }
                }




        return list;
       }

public void chulipygx( int a, int id, int id1) throws SQLException {
    CountDownLatch latch=new CountDownLatch(1);
    new Thread(new Runnable() {
        @Override
        public void run() {
            try {Class.forName("com.mysql.jdbc.Driver").newInstance();}
            catch (IllegalAccessException | ClassNotFoundException | InstantiationException e) {
                throw new RuntimeException(e);}
            try {connection[0]= DriverManager.getConnection("jdbc:mysql://sh-cdb-9761pjk2.sql.tencentcdb.com:29081/youqi","root","asd123456");
                if(a==1)
                {
                    String sql="UPDATE gx SET py =? WHERE id=? AND id1=?";
                    PreparedStatement ps=connection[0].prepareStatement(sql);
                    ps.setInt(1,1);
                    ps.setInt(2,id);
                    ps.setInt(3,id1);
                    ps.execute();
                    String sql1="UPDATE gx SET py =? WHERE id=? AND id1=?";
                    PreparedStatement ps1=connection[0].prepareStatement(sql1);
                    ps1.setInt(1,1);
                    ps1.setInt(2,id1);
                    ps1.setInt(3,id);
                    ps1.execute();


                }
                else
                { String sql="DELETE FROM gx WHERE id=? AND id1=?";
                    PreparedStatement ps=connection[0].prepareStatement(sql);
                    ps.setInt(1,a);
                    ps.setInt(2,id1);
                    ps.execute();

                }

            } catch (SQLException e) {throw new RuntimeException(e);}
            latch.countDown();
        }
    }).start();
    try{
        latch.await();
        connection[0].close();
    }
    catch (InterruptedException e)
    {e.printStackTrace();}


}
   public void deletexiaoxijilu(int id,int id1) throws SQLException {
       CountDownLatch latch=new CountDownLatch(1);
       new Thread(new Runnable() {
           @Override
           public void run() {
               try {Class.forName("com.mysql.jdbc.Driver").newInstance();}
               catch (IllegalAccessException | ClassNotFoundException | InstantiationException e) {
                   throw new RuntimeException(e);}
               try {connection[0]= DriverManager.getConnection("jdbc:mysql://sh-cdb-9761pjk2.sql.tencentcdb.com:29081/youqi","root","asd123456");
                   String sql="DELETE FROM qxq WHERE id=? AND id1=?";
                   PreparedStatement ps=connection[0].prepareStatement(sql);
                   ps.setInt(1,id);
                   ps.setInt(2,id1);
                   ps.execute();
                   String sql1="DELETE FROM qxq WHERE id=? AND id1=?";
                   PreparedStatement ps1=connection[0].prepareStatement(sql);
                   ps1.setInt(1,id1);
                   ps1.setInt(2,id);
                   ps1.execute();

               } catch (SQLException e) {throw new RuntimeException(e);}
               latch.countDown();
           }
       }).start();
       try{
           latch.await();
           connection[0].close();
       }
       catch (InterruptedException e)
       {e.printStackTrace();}

   }
  public void insertxiaoxijilu(int id,int id1,int sum,String neirong,String touxiang) throws SQLException {
      CountDownLatch latch=new CountDownLatch(1);
      new Thread(new Runnable() {
          @Override
          public void run() {
              try {Class.forName("com.mysql.jdbc.Driver").newInstance();}
              catch (IllegalAccessException | ClassNotFoundException | InstantiationException e) {
                  throw new RuntimeException(e);}
              try {connection[0]= DriverManager.getConnection("jdbc:mysql://sh-cdb-9761pjk2.sql.tencentcdb.com:29081/youqi","root","asd123456");
                  String sql="INSERT INTO lishixiaoxi (id, id1, sum, neirong, touxiang) VALUES (?,?,?,?,?)";
                  PreparedStatement ps=connection[0].prepareStatement(sql);
                  ps.setInt(1,id);
                  ps.setInt(2,id1);
                  ps.setInt(3,sum);
                  ps.setString(4,neirong);
                  ps.setString(5,touxiang);
                  ps.execute();

              } catch (SQLException e) {throw new RuntimeException(e);}
              latch.countDown();
          }
      }).start();
      try{
          latch.await();
          connection[0].close();
      }
      catch (InterruptedException e)
      {e.printStackTrace();}

           }

     public List<xiaoxijilu> getxiaoxijilu ( int id, int id1) throws SQLException {
       List<xiaoxijilu> list =new ArrayList<>();
         CountDownLatch latch=new CountDownLatch(1);
         new Thread(new Runnable() {
             @Override
             public void run() {
                 try {Class.forName("com.mysql.jdbc.Driver").newInstance();}
                 catch (IllegalAccessException | ClassNotFoundException | InstantiationException e) {
                     throw new RuntimeException(e);}
                 try {connection[0]= DriverManager.getConnection("jdbc:mysql://sh-cdb-9761pjk2.sql.tencentcdb.com:29081/youqi","root","asd123456");

                     String sql="SELECT * FROM lishixiaoxi WHERE id=? AND id1=? ";
                     PreparedStatement ps=connection[0].prepareStatement(sql);
                     ps.setInt(1,id);
                     ps.setInt(2,id1);
                     ResultSet rs=ps.executeQuery();
                     if(rs!=null)
                     {   while (rs.next())
                     {   xiaoxijilu xiaoxijilu=new xiaoxijilu() ;
                         xiaoxijilu.setId(rs.getInt("id"));
                         xiaoxijilu.setId1(rs.getInt("id1"));
                         xiaoxijilu.setSum(rs.getInt("sum"));
                         xiaoxijilu.setNeirong(rs.getString("neirong"));
                         xiaoxijilu.setTouxiang(rs.getString("touxiang"));
                         list.add(xiaoxijilu);
                     }
                     }
                     String sql1="SELECT * FROM lishixiaoxi WHERE id=? AND id1=?";
                     PreparedStatement ps1=connection[0].prepareStatement(sql1);
                     ps1.setInt(1,id1);
                     ps1.setInt(2,id);
                     ResultSet rs1=ps1.executeQuery();
                     if(rs1!=null)
                     {   while (rs1.next())
                     {   xiaoxijilu xiaoxijilu=new xiaoxijilu() ;
                         xiaoxijilu.setId(rs1.getInt("id"));
                         xiaoxijilu.setId1(rs1.getInt("id1"));
                         xiaoxijilu.setSum(rs1.getInt("sum"));
                         xiaoxijilu.setNeirong(rs1.getString("neirong"));
                         xiaoxijilu.setTouxiang(rs1.getString("touxiang"));
                         list.add(xiaoxijilu);
                     }
                     }
                     if(list.size()>0)
                     {Collections.sort(list, new Comparator<xiaoxijilu>() {
                         @Override
                         public int compare(xiaoxijilu o1, xiaoxijilu o2) {
                             return o1.getSum()-o2.getSum();
                         }
                     });}
                 } catch (SQLException e) {throw new RuntimeException(e);}
                 latch.countDown();
             }
         }).start();
         try{
             latch.await();
             connection[0].close();
         }
         catch (InterruptedException e)
         {e.printStackTrace();}

         return list;
           
     }
}
